package site.zhanjingbo.HelloDB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import site.zhanjingbo.model.User;

public class SQLInjection {
	static final String DRIVER_NAME = "com.mysql.jdbc.Driver";
	static final String DB_URL = "jdbc:mysql://localhost:3307/cloud_study?characterEncoding=utf8";
	static final String DB_USER = "root";
	static final String DB_PASSWORD = "";

	/**
	 * 示范SQL注入
	 * 
	 * @param username
	 * @param password
	 */
	public static User SQLInjectionBug(String username, String password) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		User user = null;
		try {
			Class.forName(DRIVER_NAME);
			conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(
					"select * from user where username='" + username + "' and password='" + password + "'");
			while (rs.next()) {
				user = new User();
				user.setId(rs.getInt("Id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setSex(rs.getBoolean("sex"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e2) {
			}
		}
		return user;
	}

	public static User SQLInjectionBugFix(String username, String password) {
		User user = null;
		Connection conn = null;
		PreparedStatement ptmt = null;
		ResultSet rs = null;

		try {
			Class.forName(DRIVER_NAME);
			conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
			ptmt = conn.prepareStatement("select * from user where username=? and password = ?");
			ptmt.setString(1, username);
			ptmt.setString(2, password);
			rs = ptmt.executeQuery();
			while (rs.next()) {
				user = new User();
				user.setId(rs.getInt("Id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setSex(rs.getBoolean("sex"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (ptmt != null) {
					ptmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e2) {
			}
		}

		return user;
	}

	/**
	 * 测试用例
	 * @param args
	 */
	public static void main(String[] args) {
		User user = null;
		user = SQLInjectionBug("张三", "123456");
		System.out.println(user);
		user = SQLInjectionBug("张三';-- ", "");
		System.out.println(user);
		user = SQLInjectionBug("注入' or 1=1;-- ", "");
		System.out.println(user);
		System.out.println("---bug fix--");
		user = SQLInjectionBugFix("张三", "123456");
		System.out.println(user);
		user = SQLInjectionBugFix("张三';-- ", "");
		System.out.println(user);
		user = SQLInjectionBugFix("注入' or 1=1;-- ", "");
		System.out.println(user);
		
	}

}
